Forum Discussion

InsightSeeker's avatar
InsightSeeker
Icon for Helper III rankHelper III
2 years ago
Solved

Need help with measure

I have got tables through between which I need to create a measure to get the desired results.

 

Conditional Logic:

  • The IF statement checks if:
    • Table 1 Customer equals Table 2 Customer
    • Table 1 Tool equals "Yes"
    • Type equals "Online"
  • If all conditions are met, it returns table 1 Type
  • If any condition is not met, it returns BLANK().

 

Table 1

 

CustomerToolType
000000100YesOracle
000100160NoNot Applicable
000100160YesSAP
000100161NoNot Applicable
NYC2023YesOracle
000100400NoNot Applicable
000101800YesOracle
000103900YesSAP
000103901YesSAP
000103902NoNot Applicable
000103902NoNot Applicable
000104500NoNot Applicable
000104500NoNot Applicable
000106400YesSAP
000106500YesOracle

 

(Table 2) Result to be added in this table.

 

CustomerInv NoStatusResult
00000010012322311OnlineOracle
00000010012325209Offline 
00010016012328107Offline 
00010016012331005Offline 
00010016112333903Offline 
00010016112336801Offline 
NYC202312333903OnlineOracle
NYC202312336801Offline 
00010040012339699Offline 
00010180012342597Offline 
00010180012345495OnlineOracle
00010390012342597OnlineSAP
00010390012345495OnlineSAP
00010390112348393Offline 
00010390212351291Offline 
00010390212354189Offline 
00010450012357087Offline 
00010450012359985Offline 
00010640012362883OnlineSAP
00010650012365781Offline 

 

I have tried with the below meaure but it is not returning the desired results.

 

Type =
VAR _Table1 = SELECTEDVALUE('Table 1'[Customer])
VAR _Table2 = SELECTEDVALUE('Table 2'[Customer])
VAR _tool = SELECTEDVALUE('Table 1'[Tool])
VAR _type = SELECTEDVALUE('Table 1'[Type])
VAR _status = SELECTEDVALUE('Table 2'[Status])
RETURN
IF(
    _table1 = _table2 &&
    _tool = "Yes" &&
    _status = "Online",
    _type,
    BLANK()
)

 

Need Help
Show and Tell
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    Thank you.

     

     

    Expected result measure = 
VAR _customerselect = SELECTEDVALUE('Table 2'[Customer])
VAR _statusselect = SELECTEDVALUE('Table 2'[Status])
VAR _type =
    SUMMARIZE (
        FILTER (
            'Table 1',
            'Table 1'[Customer] = _customerselect
                && 'Table 1'[Tool] = "Yes"
        ),
        'Table 1'[Type]
    )
VAR _condition = _statusselect = "Online"
RETURN
    IF ( _condition, _type )

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    I am not sure if I uderstood your question correctly, but if you want to create calculated column, please try something like below.

    Please check the below picture and the attached pbix file.

     

     

     

    Expected result calculated column =
VAR _type =
    SUMMARIZE (
        FILTER (
            'Table 1',
            'Table 1'[Customer] = 'Table 2'[Customer]
                && 'Table 1'[Tool] = "Yes"
        ),
        'Table 1'[Type]
    )
VAR _condition = 'Table 2'[Status] = "Online"
RETURN
    IF ( _condition, _type )

     

    • InsightSeeker's avatar
      InsightSeeker
      Icon for Helper III rankHelper III
      2 years ago

      Hi Jihwan_Kim  - The result you obtained is what I need. Instead of a calculated column, can I achieve this through a measure?

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        2 years ago

        Hi,

        Please check the below picture and the attached pbix file.

        Thank you.

         

         

        Expected result measure = 
VAR _customerselect = SELECTEDVALUE('Table 2'[Customer])
VAR _statusselect = SELECTEDVALUE('Table 2'[Status])
VAR _type =
    SUMMARIZE (
        FILTER (
            'Table 1',
            'Table 1'[Customer] = _customerselect
                && 'Table 1'[Tool] = "Yes"
        ),
        'Table 1'[Type]
    )
VAR _condition = _statusselect = "Online"
RETURN
    IF ( _condition, _type )