Forum Discussion

deryansyah's avatar
deryansyah
Frequent Visitor
6 years ago
Solved

Lookup From Measure

Hi   I have 2 files, First file is my Main File, The 2nd is Store Name Files, it looks like this   FILE 1         FILE 2   STORE ID INGREDIENT INGREDIENT TYPE VALUE   STORE ID S...
Need Help
Tips and Tricks
Tutorial Requests
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi deryansyah ,

    I change the formula from MAX function to SELECTEDVALUE. Please have a try.

    Measure = 
var a = ROUND(CALCULATE(AVERAGE('Table'[VALUE]),ALLEXCEPT('Table','Table'[STORE ID])),2)
var b = SELECTEDVALUE('Table'[STORE ID])
var c = SELECTEDVALUE('Table 1'[STORE NAME])
return
b &" "& c &"   "& a

    Best Regards,

    Xue Ding

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

deryansyah's avatar
deryansyah
Frequent Visitor
6 years ago

Thanks for the reply

 

I want to create a Card Visualization like this

 

| STORE ID (card visualization) | STORE NAME (card visualization) | VALUE (card visualization) |

 

but if I use your advice, It cant show STORE NAME because it cant be averaged like STORE ID

 

pardon my english,

 

thanks in advance

v-xuding-msft's avatar
v-xuding-msft
Community Support
6 years ago

Hi deryansyah ,

If you want to add a measure into the Card visual, you could try the following formula.

Measure = var a = ROUND(CALCULATE(AVERAGE('Table'[VALUE]),ALLEXCEPT('Table','Table'[STORE ID])),2)
return
MAX('Table'[STORE ID]) &" "& MAX('Table 1'[STORE NAME])&"   "& a

 

Note: It will be always showing one value. In this formula, I calculate max value out. So it will just show max value.

 

I think you could try the way that Thim suggested. It can show the "Store name" and get the average values. I create a sample that you can reference.

  • Merge the two tables. (Table (2) and Table 1(2) are the tables duplicated by original tables. )

  • Expand column

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwNFDSUXIMCPBxBdJBvkDC0MDAQClWBy4Z4Onn6ogkb2qCIu3u4xgcDFIG1QuVMwLLBTkGIJlrhCIZHBLkGA7TaIqqD8PMWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"STORE ID" = _t, INGREDIENT = _t, #"INGREDIENT TYPE" = _t, VALUE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"STORE ID", Int64.Type}, {"INGREDIENT", type text}, {"INGREDIENT TYPE", type text}, {"VALUE", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"STORE ID"}, #"Table 1 (2)", {"STORE ID"}, "Table 1 (2)", JoinKind.LeftOuter),
    #"Expanded Table 1 (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table 1 (2)", {"STORE NAME"}, {"Table 1 (2).STORE NAME"})
in
    #"Expanded Table 1 (2)"
  • Apply & Close
Measure 2 = CALCULATE(AVERAGE('Table (2)'[VALUE]),ALLEXCEPT('Table (2)','Table (2)'[Table 1 (2).STORE NAME]))

Best Regards,

Xue Ding

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

  • deryansyah's avatar
    deryansyah
    Frequent Visitor
    6 years ago

    Thank you so much for the reply v-xuding-msft 

     

    1 more question, If I create slicer of STORE ID, how can the STORE NAME in the card you created change the store name as same as the slicer I create?

     

     

    • v-xuding-msft's avatar
      v-xuding-msft
      Community Support
      6 years ago

      Hi deryansyah ,

      I change the formula from MAX function to SELECTEDVALUE. Please have a try.

      Measure = 
var a = ROUND(CALCULATE(AVERAGE('Table'[VALUE]),ALLEXCEPT('Table','Table'[STORE ID])),2)
var b = SELECTEDVALUE('Table'[STORE ID])
var c = SELECTEDVALUE('Table 1'[STORE NAME])
return
b &" "& c &"   "& a

      Best Regards,

      Xue Ding

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