March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
128 | |
90 | |
75 | |
56 | |
53 |
User | Count |
---|---|
200 | |
104 | |
101 | |
67 | |
55 |