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
  • marcelsmaglhaes's avatar
    marcelsmaglhaes
    3 years ago

    Hi Anonymous 

    I don't know if I understand your needs, but you can write a mesaure like that:

    Measure =
    VAR __DATE = SELECTEDVALUE(d_date[data_key])
    VAR __CALC =
    CALCULATE(
                SUM(f_storage[Weight]),
                ALL(f_storage),
                f_storage[data_in_key] < __DATA && f_storage[date_out] = BLANK())
    RETURN
    __CALC


    *If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Kind Regards,
    Marcel Magalhães

  • Anonymous's avatar
    Anonymous
    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()))
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hello amitchandak ,

Thank you for the reply. Yes i also tried the same way as you have shown in the video. I get the values correct. But my goal is to get the value with a date selected. For eg.  In my case, i want to select date as 12.01 and the sum of material_KG should be 300 (sum of 10.01 & 11.01 as those material are having no dateOut).
That means selecting date as 12.01 in filter , i need to look the values before 12.01 too. I tried using All but didnt work. Do you have any suggestions for me ? 

Thanking you again in Advance 🙂