Help Creating Measure/Filtering a Card

[Anonymous]

Hi, 

I am needing to show distinct count of billed materials in a field called category3. I have a billed table(containsthe billed distinct materials field) and a material table (contains the material category 3 field that corresponds to an item id). The value is currently showing distinct materials (item id) and not the count of categories.

How would I show the distinct count of categories from items bought?

Here is my card:

Here is my visual build:

[SacheeTh]

Hi @Anonymous, 
As you r KPI card show the disticts values, to calculate the distinct count of categories (Category3) from the items bought, you need to ensure you correctly establish the relationship between the Billed table and the Material table, and then use DAX to calculate the distinct count of Category3.

Here’s how you can achieve this:

---

1. Verify the Relationship

Ensure there is a relationship between the Billed table and the Material table, likely on a common field such as ItemID.

• Go to Model View in Power BI.
• Verify that Billed[ItemID] (or equivalent field) is related to Material[ItemID].

---

2. Create the Measure

Create a DAX measure to calculate the distinct count of Category3 for items in the Billed table:

DistinctCategory3Count =
CALCULATE(
    DISTINCTCOUNT(Material[Category3]),
    TREATAS(VALUES(Billed[ItemID]), Material[ItemID])
)

Explanation:

• DISTINCTCOUNT(Material[Category3]): Counts unique categories in the Material table.
• TREATAS: Filters the Material table to only include ItemIDs present in the Billed table.

---

3. Add the Measure to Your Visual

Place the new measure (DistinctCategory3Count) in your table, card, or other visuals. It will display the distinct count of Category3 based on the items billed.

---

4. Troubleshooting Common Issues

• If the relationship between the tables isn’t correctly defined, the measure won’t work. Ensure ItemID is a common field and is linked in the model.
• Ensure there are no mismatched data types (e.g., ItemID being text in one table and a number in the other).
• If filters are applied (e.g., slicers or row-level security), verify they don’t interfere with the logic.

---

This approach will calculate the distinct count of Category3 for only those items that have been billed. Let me know if you need further clarification!

[Bibiano_Geraldo]

Hi @Anonymous ,

Please try to use the following measure:

Distinct Count of Categories = 
CALCULATE(
    DISTINCTCOUNT('Material Table'[category3]),
    'Material Table'[Item ID] IN VALUES('Billed Table'[Item ID])
)

[Anonymous]

Hi, 

Thanks for the quick response. This does not work for me. Is there a way I can use the measure "Billed Distinct Item ID" (billed order table) and the field material category3 (material table) to show the distinct material category 3 count for a customer?

[Bibiano_Geraldo]

Hi @Anonymous ,

Can you please share a sample file?

[Anonymous]

Hi, 

I am not sure how as I am using Analysis Services.

[Bibiano_Geraldo]

Hi @Anonymous ,

Its not easy to get the soluion if we dont have a sample file to make tests, but based on your descrption lets try this:
1- Make sure there is a relationship between the Billed Order Table and the Material Table via the Item ID field.

2- Update the measure:

DistinctMaterialCategoriesForCustomer = 
CALCULATE(
    DISTINCTCOUNT(Material[Category3]), 
    FILTER(
        BilledOrder, 
        NOT(ISBLANK(BilledOrder[ItemID])) && 
        RELATED(Material[ItemID]) = BilledOrder[ItemID]
    )
)

[Anonymous · Accepted solution]

DistinctCountOfCategory3Filtered =

CALCULATE(

    DISTINCTCOUNT(Materials[Material Category3 Code]),

    FILTER(

        'Billed Orders',

        'Billed Orders'[CurrentSoldToCustomerKey] IN VALUES(SoldTo[CurrentCustomerKey])

    )

)

I did this and I it works!

[Bibiano_Geraldo]

Happy that you found the solution, please consider to accept it as solution.

Thank you