CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Chicken Sandwich No Pickles
Chicken Sandwich No Pickles

Reputation: 3362

PowerBI Dax Measure Not Grouping by Day of Week

I have 2 tables. I am joining both of them via a 1 to many relationship. The KPI I want to use is in Table_1, but I'm grouping it based on a column in Table_2.

When I throw the measure onto PowerBI, it gives the same value for all rows. I created the measure like this:

sales_in_cents := (sum(Table_1[Sales]) * 100)

When I group the data using a column from Table_2, it gives me the same value for each row in Table_2.

  1. Why is this?
  2. How do I fix it?

Edit #1

It's similar to this, but I do have a relationship.

Edit #2:

Table_1 has a 1 to many relationship with Table_2 and cross filter direction is set to single.

Edit #3:

I think I need to use some sort of combination of the LookUpValue and/or RelatedValue Function.

Upvotes: 0

Views: 313

Answers (1)

Stachu
Stachu

Reputation: 1724

Table_1 has a 1 to many relationship with Table_2 and cross filter direction is set to single.

That means that Table_1 can filter Table_2, but not the other way round. This is why you get repeated values when using a column from Table_2 and a measure based on the Table_1, the filter context doesn't propagate

How to solve it

  1. Measure with TREATAS: https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/
  2. Calculated column in Table_2 that would use RELATED (as each row in Table_2 has a single, related row in Table_1, for referencing the many side you need to use RELATEDTABLE)
  3. changing the relationship to bidirectional - it can create ambiguity, so I don't recommend it

This model seems a bit peculiar, is it something like a Table_1 is monthly budgets, while Table_2 is daily sales? In general star schema is recommended in the tabular models, it makes life much easier if you can use that schema

Upvotes: 1

Related Questions