Forum Discussion

Dellis81's avatar
Dellis81
Post Prodigy
6 years ago
Solved

filter context

Hello, I am needing help with proper presentation of values.   Please follow this link to download the file.https://1drv.ms/u/s!AmBVCme14p7xlTPEccsS9EpmSk1a?e=a7Q5CX   Below is a snippit of the dat...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Having the Item in the same column and on the visual makes this trickier.  FYI that it would be simpler to just pivot out the Item ID column in a Matrix visual to get almost your desired output.

     

    However, if you want a measure that does what you want, including not showing values for when Item = Federal or State Taxes, please try this expression:

    FedTaxAmountNew =
VAR __total =
    SUMX (
        DISTINCT ( Data[Description] ),
        CALCULATE ( [SumAmount], Data[ItemId] = "Federal Taxes" )
    )
VAR __thisitem =
    SELECTEDVALUE ( Data[ItemId] )
RETURN
    IF (
        OR ( __thisitem = "Federal Taxes", __thisitem = "State Taxes" ),
        BLANK (),
        __total
    )

     

    You can just replace Federal with State for the other measure.

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

Having the Item in the same column and on the visual makes this trickier.  FYI that it would be simpler to just pivot out the Item ID column in a Matrix visual to get almost your desired output.

 

However, if you want a measure that does what you want, including not showing values for when Item = Federal or State Taxes, please try this expression:

FedTaxAmountNew =
VAR __total =
    SUMX (
        DISTINCT ( Data[Description] ),
        CALCULATE ( [SumAmount], Data[ItemId] = "Federal Taxes" )
    )
VAR __thisitem =
    SELECTEDVALUE ( Data[ItemId] )
RETURN
    IF (
        OR ( __thisitem = "Federal Taxes", __thisitem = "State Taxes" ),
        BLANK (),
        __total
    )

 

You can just replace Federal with State for the other measure.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

  • Dellis81's avatar
    Dellis81
    Post Prodigy
    6 years ago

    Great - thank you!   I was able to make work.   Plus, I even got moved over into excel and have cube functions pulling in correctly.  What I'm trying to do is create an excel invoicing system - and with your help - think this could become reality.

     

    As for your comment - pivot the items, I had tried that, but was unable to get the exact tax items to pivot, but leaving the others.    Plus, I have other reports working off this one particular table, so I opted to try it the DAX way.  But, you are right, getting the two tax items as columns, would have been much simpler.   Thanks again - this forum is great!

     

    I appreciate your help - the only way I can learn - is to learn from others.  Thank you!