Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there,
I have three filter measures (let say filter1, filter2, and filter3). I created a measure that calculates the number of events (let say the name of measure is [no of events]. I want to have a checkbox with the values "Yes" or "No" so that when we ticked the Yes filter measures get enabled and [no of events] shows the number of events after filtering the data. Otherwise (if it's ticked "No") it shows the number of events without filtering the data. Is it possible to do in Power BI?
Solved! Go to Solution.
Hi @Dunner2020
I think you can achieve your goal by building a unrelated slicer table and measure.
Due to I don't know your data model I build a sample table to have a test.
Sample Table:
Slicer Table:
Measure:
No of Event =
VAR _Checkbox =
SELECTEDVALUE ( Checkbox[Status] )
VAR _YES =
SUMX (
FILTER (
'Table',
'Table'[Category1] = "A"
&& 'Table'[Category2] = "BB"
&& 'Table'[Category3] = "Event1"
),
'Table'[QTY]
)
VAR _NO =
SUM ( 'Table'[QTY] )
RETURN
IF (
HASONEFILTER ( Checkbox[Status] ),
IF ( _Checkbox = "Yes", _YES, _NO ),
""
)
Build a card visual to show the measure result. By default it will show blank, select "No", it will show sum of all QTY without filter, select "Yes", it will show results filtered by filter1 (Category1 = "A"), filter2 (Category2 = "BB") and filter3 (Category3 = "Event1").
Result is as below.
Select No:
Select Yes:
You can download the pbix file from this link: Enabling filter by check box
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Dunner2020
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
Hi @Dunner2020
I think you can achieve your goal by building a unrelated slicer table and measure.
Due to I don't know your data model I build a sample table to have a test.
Sample Table:
Slicer Table:
Measure:
No of Event =
VAR _Checkbox =
SELECTEDVALUE ( Checkbox[Status] )
VAR _YES =
SUMX (
FILTER (
'Table',
'Table'[Category1] = "A"
&& 'Table'[Category2] = "BB"
&& 'Table'[Category3] = "Event1"
),
'Table'[QTY]
)
VAR _NO =
SUM ( 'Table'[QTY] )
RETURN
IF (
HASONEFILTER ( Checkbox[Status] ),
IF ( _Checkbox = "Yes", _YES, _NO ),
""
)
Build a card visual to show the measure result. By default it will show blank, select "No", it will show sum of all QTY without filter, select "Yes", it will show results filtered by filter1 (Category1 = "A"), filter2 (Category2 = "BB") and filter3 (Category3 = "Event1").
Result is as below.
Select No:
Select Yes:
You can download the pbix file from this link: Enabling filter by check box
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey @Dunner2020 ,
Regarding your question, I have to admit that I have my problems understanding your requirement.
Please consider to create a pbix file that contains sample data, but still reflects the data model (https://docs.microsoft.com/en-us/learn/modules/design-model-power-bi/).
You can use this to enter data directly in Power BI Desktop: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-enter-data-directly-into-desktop. Upload the pbix file to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.
Create columns that contain the expected results, use the sample data to explain how the result is derived from the sample data.
Regards,
Tom
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
85 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |