Measure to show Min and Max week Number from what is shown on Visual

[Anonymous]

Hi,

Need help with Dax, I have a 3 dimension DimStore, DimComp , DimDate with one fact table FactSales. I want to Measure which returns the min week and max week number by each store and compstatusdescription.

Based on the screeshot I attached for storeNumber 600 and compStatusDesctiption Comp Min week should be 5 and Max week is 14 . For Non comp the Min week should return 1 and Max week as 4.

Any help is appreciated, Thanks!!

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create a column in fact table

related_weeknum = RELATED('date'[weeknum])

Then create measures

Max = CALCULATE(MAX('fact'[related_weeknum]),ALLEXCEPT('fact','fact'[comp],'fact'[store]))

Min = CALCULATE(Min('fact'[related_weeknum]),ALLEXCEPT('fact','fact'[comp],'fact'[store]))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Anonymous  - You need to start with the data model - Presumably your dimension tables don't filter each other. One possibility (not recommended) is to set up bi-directional filtering between the fact and date dimension. Another possibility is to add a Calculated Column to the fact table, like this:

weekNumber = RELATED(DimDate[weekNumber])

And then create Measures like this:

Max Week = MAX(FactSales[weekNumber])
Min Week = MIN(FactSales[weekNumber])

Hope this helps,

Nathan

[Cmcmahan]

Sure, this is easy enough. You'll need 4 measures, one for CompMinWeek, CompMaxWeek, NoncompMinWeek, & NoncompMaxWeek.  Change the values as necessary for each one:

CompMinWeek = CALCULATE( MIN(DimDate[weekNumber]), FactSales[CompStatusDescription] = "Comp")

Depending on how your tables are related to each other, you may need to edit this a little more heavily.  If you have further questions about any errors you get, please include a screenshot or the full text of the error and your table relationships.