Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have a column with values as multiple strings as below
I need to show the count of strings in the table or matrix and write a measure as below
Modules =
SWITCH(
TRUE(),
"Adaptive", CALCULATE ( [Customers], USERELATIONSHIP(Person[ID],Customer[ID]),FILTER ( ALL ( Customer[AddOn] ), CONTAINSSTRING(Customer[AddOn], "Adaptive") )),
"Module", CALCULATE ( [Customers], USERELATIONSHIP(Person[ID],Customer[ID]),FILTER ( ALL ( Customer[AddOn] ), CONTAINSSTRING(Customer[AddOn], "Module") )),
"BOM", CALCULATE ( [Customers], USERELATIONSHIP(Person[ID],Customer[ID]),FILTER ( ALL ( Customer[AddOn] ), CONTAINSSTRING(Customer[AddOn], "BOM") )),
"Split", CALCULATE ( [Customers], USERELATIONSHIP(Person[ID],Customer[ID]),FILTER ( ALL ( Customer[AddOn] ), CONTAINSSTRING(Customer[AddOn], "Split") ))
When adding this measure to the table not able to see anything in the table. How to catch Adaptive, Module, BOM and Split as measures and use them as rows for the table or matrix to visualize.
I have aroung 25 string writing and managing separate measures is time-consuming and maintaining is hectic. Please let me know if to have one measure to manage these strings.
Here's how you can create a single DAX measure to capture and display the count of customers for "Adaptive," "Module," "BOM," and "Split" in a table or matrix:
Count by AddOn = VAR SelectedValue = SELECTEDVALUE('YourTableName'[AddOn]) RETURN IF( ISBLANK(SelectedValue), CALCULATE( DISTINCTCOUNT('YourTableName'[PersonID]), USERELATIONSHIP(Person[ID], Customer[ID]), ALLEXCEPT(Customer, Customer[AddOn]) ), CALCULATE( DISTINCTCOUNT('YourTableName'[PersonID]), USERELATIONSHIP(Person[ID], Customer[ID]), FILTER(ALL(Customer), Customer[AddOn] = SelectedValue) ) )
Explanation:
Steps to use the measure:
Benefits:
Additional notes:
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
Power BI does not support dynamic buckets. You need to bring a precompiled list of all possible bucket names, and then you can use measures to fill these buckets, for example in the rows or the columns of a matrix visual.
Thanks for the reply. Can you suggest me one of the possible clean way to get the separate strings for each user since these are 25 different strings and each user might have two or more. Do I need to get them as a separate table with user id and each user has 25 columns representing whether each string is belonged to the user or not. Or is there any other simple way possible.
The actual implementation is up to you. Just know that you need to prepare all possibilities beforehand.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
105 | |
99 | |
99 | |
38 | |
37 |
User | Count |
---|---|
157 | |
120 | |
74 | |
72 | |
63 |