DAX issue

[subho220]

Helloss,

 Can anyone tell me the solution of the below mention Problem ? ->

I have a calculated column in my fact table, which is -

Test Request Age_New = DATEDIFF('FACT_Lab_Test_Request'[LIMS_Lab_Test_Request_Created_Date].[Date],NOW(),DAY)-if(ISBLANK(COUNTROWS(FILTER(HolidayList,AND(HolidayList[Date]>='FACT_Lab_Test_Request'[LIMS_Lab_Test_Request_Created_Date].[Date],HolidayList[Date]<=NOW())))),0,COUNTROWS(FILTER(HolidayList,AND(HolidayList[Date]>='FACT_Lab_Test_Request'[LIMS_Lab_Test_Request_Created_Date].[Date],HolidayList[Date]<=NOW()))))

Now I want to create the same column in my Orders dimension table. when i am trying to use the same calculation it gives me the below error -

also by applying Min or Max is not giving me correct result.

The relationship between Fact and Dim_order is Many to One and Single way.

What can be done in this case?

[v-kelly-msft · Accepted solution]

Hi @subho220 ,

Modify your measure as below:

Test Request Age_New = 
DATEDIFF (
    MAX('FACT_Lab_Test_Request'[Date]),
    NOW (),
    DAY
)
    - IF (
        ISBLANK (
            COUNTROWS (
                FILTER (
                    HolidayList,
                    AND (
                        HolidayList[Date] >= MAX('FACT_Lab_Test_Request'[Date]),
                        HolidayList[Date] <= NOW ()
                    )
                )
            )
        ),
        0,
        COUNTROWS (
            FILTER (
                HolidayList,
                AND (
                    HolidayList[Date] >= MAX('FACT_Lab_Test_Request'[Date]),
                    HolidayList[Date] <= NOW ()
                )
            )
        )
    )

And you will see:

For the related .pbix file,pls see attached.

[amitchandak]

@subho220 , if you want to copy value from one table to another refer this video : https://www.youtube.com/watch?v=czNHt7UXIe8

[parry2k]

@subho220 Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490