Forum Discussion

inmingoon's avatar
inmingoon
Frequent Visitor
3 years ago
Solved

Differences in using dates from FACT TABLE and DIMENSION TABLE

hello I'm posting because I'm having a lot of trouble creating a measure. My model has four tables. 1. DATA : Quantity, Symbol, Date 2. PRICE : Price, Symbol, Date 3. DATE : date 4. SYMBOL : SY...
Need Help
  • parry2k's avatar
    parry2k
    3 years ago

    inmingoon I believe this is what you are looking for, basically, you need to work with the date table in each expression:

     

    Max PRICE = 
VAR SLT_DAT = MAX ( 'Date'[DATE] )
VAR SUM_TBL = SUMMARIZE ( 'DATA', 'SYMBOL'[NAME] )
VAR PRC_DAT = 
CALCULATE ( 
    MAX ( 'PRICE'[DATE] ),
    FILTER ( 
        ALLSELECTED ( 'DATE'[Date] ),
        'DATE'[DATE] <= SLT_DAT
    ), 
    SUM_TBL
)
VAR result = 
CALCULATE (
    MAX ('PRICE'[PRICE] ),
    SUM_TBL, 
    'Date'[DATE] = PRC_DAT
)

return result

     

  • parry2k's avatar
    parry2k
    3 years ago

    inmingoon you can also take advantage of the LASTNONBLANKVALUE function, Check the video here LASTNONBLANKVALUE and Missing Data - Power BI - YouTube

     

    Max PRICE 2 = 
VAR SUM_TBL = SUMMARIZE ( 'DATA', 'SYMBOL'[NAME] )
VAR result = 
CALCULATE (
    LASTNONBLANKVALUE ( 'Date'[DATE], SUM ( 'PRICE'[PRICE] ) ),
    SUM_TBL, 
    FILTER ( 
        ALLSELECTED ( 'DATE'[Date] ),
        'DATE'[DATE] <= MAX ( 'DATE'[DATE] )
    )
)

return result

      

parry2k's avatar
parry2k
Icon for Super User rankSuper User
3 years ago

inmingoon your ask is a bit confusing. You mentioned you want to use the date from the date table, which makes sense, and then you said using the date from the date table is working fine. What we are solving here?

 

This measure works well if I construct the model like in #1 (PUT DATE from dimension TBL).
However, it doesn't work if I configure it like number 2 (PUT DATE from fact TBL).

How can I modify it to create a measure that works well in Configuration 2?
The reason I want to make it like configuration #2 is that I think it's preferable to use DATE from the DATE table for the time intelligence to work well.

 

  • inmingoon's avatar
    inmingoon
    Frequent Visitor
    3 years ago

    Thank you for your reply.

    What I mean is that if I place "the date" in the right table on the visualization from the "DATA" (fact) table, the measure works fine.(Fig.1) But if I place "the date" from the "DATE" (dimension) table, the measure doesn't work.(Fig.2, some rows have empty PRICE column.) Please look closely at the figure.

    In order to properly implement the time intelligence, I believe that "the date" should come from the "DATE" table, so I asked for help on how to modify the measure so that Figure 2 works.