Forum Discussion

xhurit's avatar
xhurit
Frequent Visitor
5 years ago
Solved

Turn off dimension rows

Hi,

 

I have a report which should be showing only fact data for stores that were open in selected date period on Date slicer.

 

Model contains Fact table and Store dimension, where each store has column for DATE_OPEN and DATE_CLOSE.

I could create a flag measure to show only stores that were opened in that period and then filter visual for FLAG = 1. But I also have to do some calculations like average sales per store where I need to display number of active stores in selected range so I cant rely strictly on fact data since not every store have facts for each date. 

 

How should I model this data and what should be a DAX code for that?

 

Kind regards and thanks in advance.

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, xhurit 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Store:

     

    Fact:

     

    Calendar(a calculated table):

    Calendar = CALENDARAUTO()

     

    Relationships:

     

    You may create meausres like below.

    Number of active store = 
var tab =
CALCULATETABLE(
    DISTINCT(Store[Store]),
    FILTER(
        ALL(Store),
        NOT(
            OR(
                [Date_Open]>MAX('Calendar'[Date]),
                [Date_Close]<MIN('Calendar'[Date])
            )
        )
    )
)
return
COUNTROWS(
    tab
)

     

    Visual Control = 
var startdate = SELECTEDVALUE(Store[Date_Open])
var enddate = SELECTEDVALUE(Store[Date_Close])
var mindate = MIN('Calendar'[Date])
var maxdate = MAX('Calendar'[Date])
return
IF(
    NOT(
        OR(
            enddate<mindate,
            startdate>maxdate
        )
    ),
    1,0
)

     

    Avg = AVERAGE('Fact'[Sales])

     

    Then you may put 'Visual Control' in the visual level filter and use 'Date' column from 'Calendar' table to filter the result.

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    xhurit , With help from date table try a measure like

    Current Store = CALCULATE(COUNTx(FILTER(Store,Store[Start Date]<=max('Date'[Date]) && (ISBLANK(Store[End Date]) || Store[End Date]>max('Date'[Date]))),(Store[Store Id ])))

     

     

    Store and date tables are not joined

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, xhurit 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Store:

     

    Fact:

     

    Calendar(a calculated table):

    Calendar = CALENDARAUTO()

     

    Relationships:

     

    You may create meausres like below.

    Number of active store = 
var tab =
CALCULATETABLE(
    DISTINCT(Store[Store]),
    FILTER(
        ALL(Store),
        NOT(
            OR(
                [Date_Open]>MAX('Calendar'[Date]),
                [Date_Close]<MIN('Calendar'[Date])
            )
        )
    )
)
return
COUNTROWS(
    tab
)

     

    Visual Control = 
var startdate = SELECTEDVALUE(Store[Date_Open])
var enddate = SELECTEDVALUE(Store[Date_Close])
var mindate = MIN('Calendar'[Date])
var maxdate = MAX('Calendar'[Date])
return
IF(
    NOT(
        OR(
            enddate<mindate,
            startdate>maxdate
        )
    ),
    1,0
)

     

    Avg = AVERAGE('Fact'[Sales])

     

    Then you may put 'Visual Control' in the visual level filter and use 'Date' column from 'Calendar' table to filter the result.

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.