Forum Discussion

sleopol's avatar
sleopol
Frequent Visitor
2 years ago
Solved

Question about Filters and variables in PowerBI

Good afternoon, I have been working on a powerbi measure for the last few days that would check the count of a date field, and filters based on specific values in other tables columns.

The issue I'm facing is when using the variables for the max Ref_doc_num, and Max Source|purdocNum|PurdocItemnum fields. To give some background there exists a relationship between supplierschagreement and SAPLVMIN but it is a many to many relationship on the source field.  (SAPLVMIN is a calculated table and based off of another table we have pulling into our dataset)

When I add in the if clause of where the REFDOCNUM = SCHLINECOUNTER and the two sources being equal as well it causes a blank in my check table for this measure however I know there are cases where the two fields should be equal. The measure works fine without this equality check. What I wanted to ask the community here about is if its possible to do an equality check like the below between two preset variables in DAX? I have never directly compared two variables like this before, but I couldn't seem to find another option. Is there a better way to compare two columns directly that reside within different tables in DAX?

Dax Formula:

Schedulingagrmnts TotalsTest =

VAR Category = Max(SupplierSchAgreement[Pur Doc Cat])

 VAR REFDOCNUMBER = MAX(SAPLVMIN[Ref_Doc_Num])

 VAR SCHLINECOUNTER = MAX(SupplierSchAgreement[Delivery Sch Line Counter])

 VAR SCHAGRMNTSOURCE = MAX(SupplierSchAgreement[Source|PurDocNum|PurDocItemNum])

 VAR SAPLVMINSOURCE = MAX(SAPLVMIN[Source|PurDocNum|PurDocItemNum])

 VAR SupplierType = MAX(SupplierList[Supplier Type])

RETURN IF(Category = "Scheduling Agreement" && REFDOCNUMBER = SCHLINECOUNTER && SCHAGRMNTSOURCE = SAPLVMINSOURCE, CALCULATE(
    COUNT(SupplierSchAgreement[Latest Goods Receipt Date]),
    SupplierSchAgreement[Open Quantity] = 0,USERELATIONSHIP(DateKey[Date],SupplierSchAgreement[Latest Goods Receipt Date]),SupplierSchAgreement[SchType] = "Firm",SupplierList[Supplier Type] = "Dana"))


Relationship Example:

 




  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi sleopol 

     

    It seems that you want to create a link between two table which relationship can only be many-to-many.

    Maybe you can consider the crossjoin() function to create a new table and use the filter() function to filter the value you need.

    Here is a sample:

    Here are two table:

    Then create a new table:

    Table = FILTER(CROSSJOIN('ORDER','QT'),'ORDER'[ID]='QT'[_ID])

    The result is as follow:

    It is worth noting that these two table can't have the same column name.

    Link of crossjoin():

    CROSSJOIN function (DAX) - DAX | Microsoft Learn

    Best Regards

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

2 Replies

  • Wilson_'s avatar
    Wilson_
    Icon for Memorable Member rankMemorable Member

    sleopol,

     

    Can you please share a sample pbix file? (If you don't know how, please check the pinned thread in the forum.) It would make debugging your issue easier. 🙂

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi sleopol 

     

    It seems that you want to create a link between two table which relationship can only be many-to-many.

    Maybe you can consider the crossjoin() function to create a new table and use the filter() function to filter the value you need.

    Here is a sample:

    Here are two table:

    Then create a new table:

    Table = FILTER(CROSSJOIN('ORDER','QT'),'ORDER'[ID]='QT'[_ID])

    The result is as follow:

    It is worth noting that these two table can't have the same column name.

    Link of crossjoin():

    CROSSJOIN function (DAX) - DAX | Microsoft Learn

    Best Regards

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