Switch between daily and monthly fact tables with slicer

[jaryszek]

Hi everyone,

I’m trying to build a single visual that can switch between a monthly and a daily view of amortized cost using a slicer.

I have a dim_date table with columns Date, Year, MonthName, Day, and a date hierarchy (Year  MonthName  Day). Then I have two fact tables: fct_amortized_monthly (one row per month, field MonthDate) and fct_amortizedcosts_daily (one row per day, field Date).

Relationships:

I created two measures that both work fine on their own:

Total Amortized Cost (Daily) = 
    SUM ( 'fct_amortizedcosts_daily'[CostInBillingCurrency] )

Total Amortized Cost (Monthly) = 
    SUM ( 'fct_amortized_monthly'[CostInBillingCurrency] )

And also have a parameter table like here which i put into slicer:

Date Hierarchy = {
    ("Monthly", NAMEOF('dim_date'[MonthName]), 0),
    ("Day", NAMEOF('dim_date'[Day]), 1)
}

and this is my measure which I created on the visual whihc throwing an error:

Total Amortized Cost =
VAR selItem =
    SELECTEDVALUE('Date Hierarchy'[Date Hierarchy])
RETURN
SWITCH(
    TRUE(),
    selItem = "Monthly", [Total Amortized Cost (Monthly)],
    selItem = "Daily", [Total Amortized Cost (Daily)],
    BLANK()  -- default if neither matches
)

Why this is not working? 
What is happening? 

Link to example model:

https://drive.google.com/file/d/1rtcgE1fX376PPPqVtwzCb9sOmJr_I86R/view?usp=sharing

Best,
Jacek

[Ray_Minds]

Answer:
Your measure is almost correct the issue is with the slicer value names. In your parameter table, you used "Monthly" and "Day", but your SWITCH function checks "Monthly" and "Daily".
Use exactly the same text as in your slicer:

Total Amortized Cost =
VAR sel = SELECTEDVALUE('Date Hierarchy'[Date Hierarchy])
RETURN
SWITCH(
    sel,
    "Monthly", [Total Amortized Cost (Monthly)],
    "Day", [Total Amortized Cost (Daily)],
    BLANK()
)

Also ensure:
  • 'fct_amortizedcosts_daily'[Date] → 'dim_date'[Date]
  • 'fct_amortized_monthly'[MonthDate] → 'dim_date'[Date] (should be month start date)
This will allow the visual to correctly switch between daily and monthly views using your slicer.
If you also want to switch the axis (like between Date and Month), use a Field Parameter.

Please try this solution and let me know if it works or share your feedback.

[ajaybabuinturi]

Hi @jaryszek,

I have comup with different approach not sure how it will suites your business requirement. Please follow the below steps to achieve your requirement.

1. Create Date Granularity table as follows

Date Granularity = UNION(
ADDCOLUMNS(CALENDAR(FIRSTDATE(dim_date[Date]),LASTDATE(dim_date[Date])),"grain_date",[Date],"type","Daily","Order",1),

ADDCOLUMNS(CALENDAR(FIRSTDATE(dim_date[Date]),LASTDATE(dim_date[Date])),"grain_date",DATE(YEAR([Date]),MONTH([Date]),1),"type","Monthly","Order",2)
)

2. Create a relation bewteen dim_date and Date Granularity tables (dim_date[date] (1) <----> Date Granularity[grain_date](*) ) and set cross filter direction to Both

3. Modify the Total Amortized Cost measure as below

Total Amortized Cost(Revised) = 
VAR selItem =
    SELECTEDVALUE('Date Granularity'[type])
RETURN
SWITCH(
    TRUE(),
    selItem = "Monthly", [Total Amortized Cost (Monthly)],
    selItem = "Daily", [Total Amortized Cost (Daily)],
    BLANK()  -- default if neither matches
)

 4. Create the visual as below

Here is the .pbix file: https://drive.google.com/file/d/1U55GnF0Xl5pYa-fAHXO3PPezhqd4xlzv/view?usp=drive_link 

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[jaryszek]

Thank you very much, 

I am trying to avoid bi-directional relatioships...

Best,
Jacek

[MFelix]

Hi @jaryszek ,

The problem is that when you use Field parameters the combination of the parameter order + the parameter name being a composite key is what give the calculation value, in this case if you simply change the measure to pickup the order you get the correct result:

Total Amortized Cost = 
VAR selItem =
    SELECTEDVALUE('Date Hierarchy'[Parameter Order])
RETURN
SWITCH(
    TRUE(),
    selItem = 0, [Total Amortized Cost (Monthly)],
    selItem = 1, [Total Amortized Cost (Daily)],
    BLANK()  -- default if neither matches
)

Another option is to create a new column on your parameter table that has the same value has the  Date Hierarchy column then you can use it:

Total Amortized Cost = 
VAR selItem =
    SELECTEDVALUE('Date Hierarchy'[Name])
RETURN
SWITCH(
    TRUE(),
    selItem = "Monthly", [Total Amortized Cost (Monthly)],
    selItem = "Day", [Total Amortized Cost (Daily)],
    BLANK()  -- default if neither matches
)

This new column is not a composite key so returns the correct value:

Please see file attach

[jaryszek]

Wow thank you very much. It is clear!!

One question, can I move my AmortizedCost measure somehow to calculation group to not switch statement within Measure? 

Best,
Jacek

[MFelix · Accepted solution]

Hi @jaryszek ,

For that you would need to use the switch measure on the calculation group basically is the same syntax the difference is were it's located.

What you can do is a different setup using field parameters also.

• Create a Field Parameter with your two measures
• Add a field with the values for the Day and Monthly

• Related both parameter tables:

• Setup the visual like this:
  • X-axis: Date Hierarchy
  • Y-Axis: Measures Selection

Result:

Please see file attach.

[jaryszek]

Really appriaciated,

thank you very much!!

[DNMAF]

Hi @jaryszek ,

I found this phantastic webpage that provides a solution for your issue and a short explanation. It works!

Kudos to @marcorusso 

Here is a piece of code. Instead of 

VAR selItem =
    SELECTEDVALUE('Date Hierarchy'[Date Hierarchy])

you should write:

VAR tempselItem = SELECTCOLUMNS (

        SUMMARIZE ( 'Date Hierarchy', 'Date Hierarchy'[Date Hierarchy], 'Date Hierarchy'[Parameter Fields] ),

        'Date Hierarchy'[Date Hierarchy]

    )

VAR selItem = IF ( COUNTROWS ( tempselitem ) = 1, tempselitem )

Hope that helps!

Oh, and there seems to be a typo in your code: You write "Daily" instead of "Day"

[jaryszek]

thank you very much