Forum Discussion
Filter a distinct count with a slicer
Hi,
I have this database,
Date | Account ID | Volume |
04/01/2021 | A1234 | 5 |
04/05/2021 | A1235 | 2 |
04/15/2021 | A1236 | 5 |
04/15/2021 | A1234 | 5 |
04/27/2021 | A1236 | 2 |
05/03/2021 | A1236 | 10 |
05/10/2021 | A1234 | 5 |
05/20/2021 | A1237 | 2 |
05/30/2021 | A1235 | 5 |
05/31/2021 | A1236 | 2 |
06/04/2021 | A1234 | 10 |
06/12/2021 | A1237 | 2 |
06/22/2021 | A1235 | 20 |
And I am using it the following way in Power BI,
Since it is not possible to make a slicer for volume in a direct way, due to the base I use, I needed to perform the sequence of actions below:
1- Create a table that generates values from 0 up to the sum value of the volume (Volume Measure)
2- Create the slicer with this table
3 - Create a measure to filter the visual table, so that it shows only the information selected in the slicer volume
4 - Create a measure to filter the volume slicer, making the maximum and minimum of the slicer according to the volumes of the IDs
This way the volume slicer can filter the visual table correctly.
But when the volume slicer is changed, the distinct count of Account IDs is not changed.
And somehow Power BI does not allow me to use the same filter that I applied on the visual table.
I need to make the volume slicer filter out the distinct count of Account IDs. Can someone please help me?
Regards,
Rafael B.
Oh, I missed this step
"Create a table that generates values from 0 up to the sum value of the volume (Volume Measure)"
You cannot create a measure table. You can only create a calculated table - that is calculated only once, during data source refresh, and cannot be influenced by the user.
Measure results need to be scalar values.
3 Replies
The distinct count of Account IDs is in a different visual. You need to apply the same filter to that visual, or set the filter to be page level.
I tried the two solutions you proposed, but Power BI didn't allow it and I don't know why.
Oh, I missed this step
"Create a table that generates values from 0 up to the sum value of the volume (Volume Measure)"
You cannot create a measure table. You can only create a calculated table - that is calculated only once, during data source refresh, and cannot be influenced by the user.
Measure results need to be scalar values.
