Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Help Creating Measure/Filtering a Card

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:

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago
    DistinctCountOfCategory3Filtered =
    CALCULATE(
        DISTINCTCOUNT(Materials[Material Category3 Code]),
        FILTER(
            'Billed Orders',
            'Billed Orders'[CurrentSoldToCustomerKey] IN VALUES(SoldTo[CurrentCustomerKey])
        )
    )

    I did this and I it works!

8 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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?

  • SacheeTh's avatar
    SacheeTh
    Icon for Resolver II rankResolver II
    1 year ago

    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!