Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Writing Dax Measure by comparing 2 columns from different tables

Hello,   I have created dummy tables for a good understanding. There are 2 tables : DimDate and Fact_Storage.        Here DimDate table has a Datekey. Fact_storage  tabl...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi marcelsmaglhaes ,
Thank you for your reply.

It is not working becasue if i use this measure _Date =SELECTEDVALUE(DimDate[DateKey])
Then it shows a cumulative sum of the DateKey something like 20.3 Million. Its not showing exact DateKey 20230112, if i have selected 12.01.2023 as a filer from Dim_Date.

The main goal is :
If i select 12.01.2023 as a filter from DimDate, then i need to sum all the dates where DateOut is blank till 12.01 (also 10.01 and 09.01).
That means the sum should be 300KG ( Material B&C as they dont have DateOut). The DateOut key by material B is written by mistake.

Do you have any other suggestions? Thanks in Advance 🙂

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

Hey Anonymous ,

 

If you need to filter a date, then selectedvalue captures the selected date and will serve as the date to look at the fact table to search and filter the values. Of course this measure needs to be improve according your visualisation needs, but is the starts.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thnx marcelsmaglhaes amitchandak ,

    The use of Crossfilter and selected value both worked. 

    This measure worked, 

    var dateshow = SELECTEDVALUE(DimDate[DateKey])
     
    return

    CALCULATE(SUM(FactStorage[MaterialWeightKG]),
    CROSSFILTER(DimDate[DateKey], FactStorage[DateInKey], None),
    FactStorage[DateInKey]<=dateshow && (FactStorage[DateOutKey]>dateshow || FactInventory[DateOutKey]=BLANK()))