I need list of measures show as rows in the table or matrix

[anusha_2023]

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.

[123abc]

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:

1. VAR SelectedValue: This variable stores the selected value from the AddOn column (if any).
2. IF statement: This statement checks if a value is selected in the AddOn column:
   • ISBLANK(SelectedValue): If no value is selected, it calculates the total customer count by removing the AddOn filter using ALLEXCEPT.
   • CALCULATE: If a value is selected, it filters the Customer table based on the selected AddOn value and then calculates the distinct count of customer IDs.

Steps to use the measure:

1. Create a new measure in Power BI Desktop.
2. Paste the provided DAX code into the measure definition.
3. Replace 'YourTableName' with the actual name of your table containing the PersonID and AddOn columns.
4. Add the Count by AddOn measure to the Rows field of your table or matrix.

Benefits:

• This single measure dynamically calculates the count for "Adaptive," "Module," "BOM," and "Split" based on user selections in the AddOn column.
• It eliminates the need to create and manage individual measures for each string value, saving time and effort.

Additional notes:

• You can customize the measure format to display the count in a preferred way, such as percentages.
• Consider using a slicer or filter on the AddOn column to allow users to interactively explore the data further.

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.

[lbendlin]

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.

[anusha_2023]

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.

[lbendlin]

The actual implementation is up to you. Just know that you need to prepare all possibilities beforehand.