The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hello,
My goal is to get a count of maximum available values in slicer (I've made a filter measure of COUNTROWS(RELATEDTABLE(FACT) and put it as a filter greater or equal to 1 for the slicer, so it captures only date available in fact table) and compare it with the current values choosen in the slicers. Then, depending on how many I'll make a switch statement with certain behaviours like
-Not selected anything - "Please select period"
-Everything selected - "All period selected (" & MaxSelectionCount & ")"
-Up to 5 selections - I have concat with MonthYear values
-Above 5 selections - Above 5 periods selected (" & Count & ")"
So far I've tried only with Not selected and Everything Selected, If I got those right then I'll be set and rest is easy
VAR _Selection =
ISFILTERED ( ' date'[MonthYear] )
VAR _Count =
COUNTROWS ( VALUES ( ' date'[MonthYear] ) )
VAR _MaxSelectionCount =
CALCULATE (
COUNTROWS ( ' date' ),
REMOVEFILTERS ( ' date'[MonthYear] ),
RELATEDTABLE ( ' FACT' )
)
VAR _Result =
SWITCH (
TRUE (),
Selection = FALSE (), "Please select period to review",
Count = MaxSelectionCount,
"All period selected (" & MaxSelectionCount & ")"
)
RETURN
Result
It is because it gives me 9, instead of 7 available values in slicer
Hi , @Pbiuserr
According to your descrition, you want to get the count of the slicer has been selected?
If this , you can use this dax code to get the count of the selection you have been selected.
COUNTROWS(VALUES(date'[MonthYear]))
And before use this , you need to use the ISFILTERED() function to check if it has beeen selected .
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly