Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
Expand all | Collapse all

Check If Column value of one table matches with column value of another table then perform operation

  • 1.  Check If Column value of one table matches with column value of another table then perform operation

    Posted Aug 23, 2022 02:32 AM
    Hi Guys,

    I have a table that is having some divisions values named tableA(this is used in matrix)
    Bakery
    Camera
    Food
    Household
    Chairs
    Fans

    I have target table ,that is having targets based on each division for this year
    Division Target_0 Target_50 Target_100 Target_150 Year
    Bakery 0.36 0.38 0.41 0.46 2022
    Camera 0.52 0.54 0.56 0.61 2022
    Food 0.48 0.5 0.52 0.57 2022
    Household 0.36 0.39 0.42 0.48 2022

    I have measure that is having these values and i need to check if divisions(table A) is present in division of target table then match it's corresponding rows to measure

    if the measure is less target_50 column then true else false.

    I created measure but this works only for one row, i was trying to compare tableA[Division] with 'Target table'[Division] directly through related also ,it isn't working

    VAR CHECK_RED=IF (FIRSTNONBLANK((tableA[Division]), 1) =FIRSTNONBLANK(('Target table'[Division]), 1)  &&
    FIRSTNONBLANK(('Yearly Targets'[Year]), 1)="2022",
    IF(FORMAT('View By'[Measure],"Percent")<FORMAT(FIRSTNONBLANK(('Target table'[Target_50_Percent]),1),"Percent"),True,False),False)
    RETURN 
    CHECK_RED

    Could u please guide me how we can compare 2 columns here? Just need comparison for 2 columns


    ------------------------------
    Prince Kumar
    Consultant- Cloud Data Engineer
    ------------------------------


  • 2.  RE: Check If Column value of one table matches with column value of another table then perform operation

    Posted 29 days ago
      |   view attached
    @Prince Kumar,

    It would be easier to answer your question if you post a .pbix file with a sample of your data and your data model.

    Based on what you described, I tried to create what I think your data and model look like.​  I think you have three tables like so:
    Division:

    Target:

    Results:

    And the data model might look like this:

    In that case, you could add two calculated columns to the Divisions table, that would have formulas like this:
    Does Division Have Target_50 = 
        NOT(ISBLANK(RELATED(target[Target_50])))
    
    
    
    
    Are results for division less than Target_50 = 
        IF(
            divisions[Does Division Have Target_50],
            IF(RELATED(results[Value]) < RELATED(target[Target_50]), "True", "False"),
            BLANK()
        )​


    And then you can create a table visual to show the results:


    Hope that helps.  Please consider posting your ,pbix file, and we will be able to give you a more customized answer.

    Kaz.



    ------------------------------
    Kaz Shakir
    Sr. Program Manager, Asset Planning
    TN
    ------------------------------

    Attachment(s)

    pbix
    princekumar_example_1.pbix   36 KB 1 version