Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello power people!
I have a list of items, and I want to a create a card visual that will display based on how much is selected (highlighted) on a table.
For Example:
1- IF the user highlighted a single item on a table visual, the card will display the item ID.
2- And IF the user highlighted multiple items, the card will display the number of highlighted items (3 Items, 4 Items, 20 items etc..).
3- And IF the user has not highlighted anything, which means all the items are selected, I want the card visual to display "ALL" word.
I already did most of the work I believe, but my issue is I don't know how to make a condition that will check if all the items are selected (my 3rd condition above).
Here is my DAX code:
Measure = VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Sales Lines (Merge)'[ItemId]) VAR __MAX_VALUES_TO_SHOW = 1 RETURN IF( __DISTINCT_VALUES_COUNT > 1000; "ALL"; IF(__DISTINCT_VALUES_COUNT > 1; CONCATENATE(__DISTINCT_VALUES_COUNT ; " Items"); VALUES('Sales Lines (Merge)'[ItemId]) ))
This code will display the ITEM ID if the user highlighted a single item, and if the user selected multiple items it will display the number of highlighted items.
But if you can see my first IF condition: IF( __DISTINCT_VALUES_COUNT > 1000; "ALL";....) this will display "ALL" IF the highlighted items are more than 1000.....
My total Items are 1500 and it can be increased by time.
I don't know how to alter my IF condition to check if ALL items were selected (highlighted).
How should I alter the IF condition? or maybe there are some kind of constant variables I can use?
Thanks in advance!
Solved! Go to Solution.
Hello @I_NeedMorePower
You can use the ISFILTERED function to test whether the ItemId column has not been filtered (by highlighting rows of the table), assuming the ItemId column is in the table.
Otherwise you could try ISCROSSFILTERED.
Something like:
Measure = VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT ( 'Sales Lines (Merge)'[ItemId] ) VAR __MAX_VALUES_TO_SHOW = 1 RETURN IF ( NOT ISFILTERED ( 'Sales Lines (Merge)'[ItemId] ); "ALL"; IF ( __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW; CONCATENATE ( __DISTINCT_VALUES_COUNT; " Items" ); VALUES ( 'Sales Lines (Merge)'[ItemId] ) ) )
Does that work in your case?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsRegards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsOh this works too!
Intresting, using calculate with ALL expression...
Thank you that was very informative!
Hello @I_NeedMorePower
You can use the ISFILTERED function to test whether the ItemId column has not been filtered (by highlighting rows of the table), assuming the ItemId column is in the table.
Otherwise you could try ISCROSSFILTERED.
Something like:
Measure = VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT ( 'Sales Lines (Merge)'[ItemId] ) VAR __MAX_VALUES_TO_SHOW = 1 RETURN IF ( NOT ISFILTERED ( 'Sales Lines (Merge)'[ItemId] ); "ALL"; IF ( __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW; CONCATENATE ( __DISTINCT_VALUES_COUNT; " Items" ); VALUES ( 'Sales Lines (Merge)'[ItemId] ) ) )
Does that work in your case?
Yes it does! Thank you for your help! I still lack the knowneldge of DAX functions, but hopefully with time I will get used to it 🙂
Thanks again!
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 |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |