Forum Discussion

tex628's avatar
tex628
Community Champion
9 years ago
Solved

Assistance needed

Hi! 

 

I'm having some issues with something im trying to create. 

 

 

I need to identify the German (DE) ID's where the price doesn't match the Swedish (SE) price for the same ID. The Norweigan (NO) are irrelevant.

I've been sitting with alot of different dax formulas but I can't seem to find a solution.

 

Help would be greatly appreciated! 

Need Help
Tutorial Requests
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi tex628,

     

    Based on my test, you should be able to use the formula below to create a calculate column in your table to identify the German (DE) ID's where the price doesn't match the Swedish (SE) price for the same ID .

    Tag = 
VAR sePrice =
    CALCULATE (
        FIRSTNONBLANK ( Table1[Price $], 1 ),
        FILTER (
            ALL ( Table1 ),
            Table1[ID] = EARLIER ( Table1[ID] )
                && Table1[Origin] = "SE"
        )
    )
RETURN
    IF ( Table1[Origin] = "DE" && Table1[Price $] <> sePrice, 1, 0 )

     

    Then you can use the tag column as visual level filter or as Slicer to show the result on the report.

     

     

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards

5 Replies

  • Back2Basics's avatar
    Back2Basics
    Resolver I
    9 years ago

    I'm not sure this needs Power BI. Can't you just do this manually? it's a very small dataset

    • tex628's avatar
      tex628
      Community Champion
      9 years ago

      I'm not able to use the accual dataset here. The one im working with contains over 50 000 rows. Sorry that I have to use a stand-in! 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    You just want to identify these and not do any further visualization or calculation?

     

    Try this,

    Create a slicer with Country (Orgin) and select SE AND DE

    Create a table visual where you put ID on values and define a new measure that will identify the ID's like this:

    DifferentPrice =
    IF(AVERAGE(Data[Price])<>MAX('Data'[Price]);1)

     

     

    All done!

     

    Br,

    Magnus

    • tex628's avatar
      tex628
      Community Champion
      9 years ago

      The price row will sometimes deal with mix of string and integer values so I cant get the datatype right and therefore im having issues using your solution. I trying different workarounds now.

       

      Br, Johannes

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi tex628,

     

    Based on my test, you should be able to use the formula below to create a calculate column in your table to identify the German (DE) ID's where the price doesn't match the Swedish (SE) price for the same ID .

    Tag = 
VAR sePrice =
    CALCULATE (
        FIRSTNONBLANK ( Table1[Price $], 1 ),
        FILTER (
            ALL ( Table1 ),
            Table1[ID] = EARLIER ( Table1[ID] )
                && Table1[Origin] = "SE"
        )
    )
RETURN
    IF ( Table1[Origin] = "DE" && Table1[Price $] <> sePrice, 1, 0 )

     

    Then you can use the tag column as visual level filter or as Slicer to show the result on the report.

     

     

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards