Forum Discussion

Hemanth96's avatar
Hemanth96
Frequent Visitor
3 years ago
Solved

Return Text column based on other column condition

Hi, I'm a noobie to DAX and need help. I have a table with product names and store wise sales.

 

I want to see a visual matrix table where the prodnames should be displayed if either store 1 or store 2 has sales. If there's no sale in either of the store for that prodname, it shouldn't appear in the table. Please help me how to do this.

 

prodnamestore1store2store 3store 4
prd110080 26
prd2 607212
prd350   
prd4  6629
prd5 81023
prd6   2868
prd7 118 11 

 

OUTPUT

 

prd#store1store2store 3store 4
prd110080 26
prd2 607212
prd350   
prd5 81023
prd7 118 11 
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Hemanth96 ，

     

    Here I suggest you to create measures by dax and add measures into visual level filter to filter your visual.

    Measure is based on your table.

    Table1:

    Measure 1 = 
IF(ISBLANK(SUM('Table'[store1]))&& ISBLANK(SUM('Table'[store2])),0,1)

    Table2:

    Measure 2 = 
VAR _STORE1 = CALCULATE(SUM('Table (2)'[Value]),FILTER('Table (2)','Table (2)'[Attribute] = "store 1"))
VAR _STORE2 = CALCULATE(SUM('Table (2)'[Value]),FILTER('Table (2)','Table (2)'[Attribute] = "store 2"))
RETURN
IF(ISBLANK(_STORE1)&&ISBLANK(_STORE2),0,1)

    Create visual by columns , add measure into visual level filter and set it to show items when value =1.

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Hemanth96 ，

     

    Here I suggest you to create measures by dax and add measures into visual level filter to filter your visual.

    Measure is based on your table.

    Table1:

    Measure 1 = 
IF(ISBLANK(SUM('Table'[store1]))&& ISBLANK(SUM('Table'[store2])),0,1)

    Table2:

    Measure 2 = 
VAR _STORE1 = CALCULATE(SUM('Table (2)'[Value]),FILTER('Table (2)','Table (2)'[Attribute] = "store 1"))
VAR _STORE2 = CALCULATE(SUM('Table (2)'[Value]),FILTER('Table (2)','Table (2)'[Attribute] = "store 2"))
RETURN
IF(ISBLANK(_STORE1)&&ISBLANK(_STORE2),0,1)

    Create visual by columns , add measure into visual level filter and set it to show items when value =1.

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    You didn't specify if you need this in Power Query or Power BI.  Here is a Power Query option.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY2xDcAgDARXiVxTYBOMMwuiywBR9i/CB1ui+Jf8p5N7p+e9mRJxzrMNdcyI0kg/FF8UqAGxBCvzrKGsODm3URXWFaj6bHgLWUogPTZNDK4Fa9iYzSnzejc+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [prodname = _t, store1 = _t, store2 = _t, #"store 3" = _t, #"store 4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"prodname", type text}, {"store1", Int64.Type}, {"store2", Int64.Type}, {"store 3", Int64.Type}, {"store 4", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [store1] ?? 0 +[store2] ?? 0 ),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Custom] <> 0))
in
    #"Filtered Rows"

     

     

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

     

    Note:  Data like this should be unpivoted before being loaded into Power BI.