Forum Discussion

RenateBK's avatar
RenateBK
Icon for Helper II rankHelper II
11 months ago
Solved

Date dimension to a table with multiple date columns calculations - ensuring it's correct set up

Hello, 

 

I am in need of a confirmation that I have set up the relationships and connections correct for my report. I am connected to a datalake for my first time so I am in slightly unknown waters. 

 

I am currently setting up a dashboard for a fact table that has 6 date columns.

 

The dim_date table has a date_key which is a 8 integer maching every single data from 1980-2030, and the date columns in my fact table are all written as 8 integer entries instead of dates so they can be connected to the dim_date table using the date_key.

(please ignore the auto sum on beslutningsdato and lopedag, I have turned that off in my semantic model now).

 

Now all the entries in the fact table are all individual cases recognised by a unique ID case number, that all has a "received day/INNDAG", "valid day" and  "expiration date". In this case I'm setting up a dashboard based on total received cases per year and facts about these cases, thus I need the dashboard and data to be filtered on "INNDAG". To ensure this, I have set up the relationdship between the tables based on date_key and INNDAG with a one to many relationship. 

 

To be able to summarize and count the amount of the cases per year I opted for a measurement with "COUNTROWS" based on the unique case_ID column in the fact table. My main issue is that my numbers aren't exactly accurate with what has been reported for prior years so I was wondering if there's something I should do differently or if my countrows measurement is correctly filtered and calculating the amount of rows based on the INNDAG with the current set up.

 

I was thinking setting up inactive relationships for the other date dimensions and utilising USERELATIONSHIP/role-playing dimensions for other analyses if necessary. 

Thank you so much for your time.

 

  • Hi RenateBK 
    If numbers are still not matching then you can try below approach

    • create a table visual
    • drag drop datekey columns from dim date and fact table
    • add your measure 
    • check if the datekeys are matching or are there any blanks
    • if there are no blanks and keys are matching try checking the numbers for a year,qtr,month

    Let me know if it helps.

4 Replies

  • Hi RenateBK 

     

    Try DISTINCTCOUNT or DISTINCTCOUNTNOBLANK on Case ID column instead of COUNTROWS.

     

    Let me know if it works.

    Please Give a Kudos or mark it as resolved if it helps.

    Thanks and Regards,

    Praful

    • RenateBK's avatar
      RenateBK
      Icon for Helper II rankHelper II

      Hello, 

       

      Thank you for your feedback, I've adjusted the measurement so it includes DISTINCTCOUNTNOBLANK now which is a nice fail safe to include in case some blank Case ID's end up in the data set. 

       

      The numbers stay the same so it doesn't seem to be anything there, it could be that the previous reporting had some lag due to it being manually exported through a UI, if there's nothing else wrong about the set up (that I described in the first post)

  • Hi RenateBK 
    If numbers are still not matching then you can try below approach

    • create a table visual
    • drag drop datekey columns from dim date and fact table
    • add your measure 
    • check if the datekeys are matching or are there any blanks
    • if there are no blanks and keys are matching try checking the numbers for a year,qtr,month

    Let me know if it helps.

    • RenateBK's avatar
      RenateBK
      Icon for Helper II rankHelper II

      Hello Praful, 

       

      Thank you so much for the advice, I have gone through it and double checked it all and it should be good now 🙂

       

      Best regards, 

      Renate