filter context

[Dellis81]

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 data - the key thing to note Item ID includes the fuel type as well as fed/state taxes.

Invoice	Invoice Date	ItemID	Description	Quantity	Amount
4152	6/20/2020	GAS	6/18/2020: JW: T004 87 Ravens: T004 87 Ravens	1	2.75
4152	6/20/2020	GAS	6/18/2020: JW: T004 87 Ravens: T004 87 Ravens	1	2.75
4152	6/20/2020	GAS	6/19/2020: JW: T004 87 Ravens: T004 87 Ravens	1	2.75
4161	6/27/2020	DIESEL	6/23/2020: JW: #50 05 Peterbilt: 50 Peterbilt	36	115.2
4161	6/27/2020	GAS	6/23/2020: JW: T004 87 Ravens: T004 87 Ravens	1	3
4161	6/27/2020	GAS	6/22/2020: JW: T004 87 Ravens: T004 87 Ravens	1	3
4161	6/27/2020	Federal Taxes	6/23/2020: JW: #50 05 Peterbilt: 50 Peterbilt	36	8.78
4161	6/27/2020	State Taxes	6/23/2020: JW: #50 05 Peterbilt: 50 Peterbilt	36	10.66

Here;s the result of my initial attempt

Using this measure 

FedTaxAmount = calculate([SumAmount],Data[ItemId]="Federal Taxes")

How can the above measure be modified to exclude the subtotal for Gas.   The common datafield between diesel fuel type and taxes is the description field.

The second question is an additional tweak to avoid improper results depending on visualization.     To prevent potential user confusion - would like to prevent these two items from showing up in rows.    In the first example, I was able to use external filters to remove from matrix, but I want to hard code the formula to prevent an unexpecting user confusion.   I will also eventually want to move this to excel, and utilize cubefunctions.

Again, here is link to the sample file.    

https://1drv.ms/u/s!AmBVCme14p7xlTPEccsS9EpmSk1a?e=a7Q5CX

thanks - and always appreciate the forum's support!

[mahoneypat · Accepted solution]

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]

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!

[jdbuchanan71]

@Dellis81 

Try change your filter to include KEEPFILTERS.

FedTaxAmount = calculate([SumAmount],KEEPFILTERS(Data[ItemId]="Federal Taxes"))

StateTaxAmount = calculate([SumAmount],KEEPFILTERS(Data[ItemId]="State Taxes"))

You can read about KEEPFILTERS in this article.

https://www.sqlbi.com/articles/using-keepfilters-in-dax/

[Dellis81]

Thanks - that got me closer, and would have worked except with the request to "exclude" the fed/state tax items in the row dimension.   The other individuals solution seemed to work better in this spot.

I appreciate the link to the artical on keepfilters.   I had read it before, but there is so much to learn - I wished I had a photographic memory to remember all this nuances.  But great stuff and thank you for your assistance.

thank you!