Hey guys,
I am doing a balance sheet, and as you know some totals are summing quite some accounts values.
I could do it with this:
Total measure = Calculate ( [totalCD], General Ledger [Account] ) IN {“41”, “51”, “25”, “26”, “27”, “28”, “29”, “31”, “32”, “33”, “34”, “35”, “36”, “37”, “38”, “39”, “61”, 65", “66”, “67” } )
Or i would prefer to do it with a parameter. Because i don’t know how, it “sounds” better with a parameter.
I saw this video from SAM (https://www.youtube.com/watch?v=XcUsniWenuM&ab_channel=EnterpriseDNA) and because i had to choose current value, don’t know if this is the way to do it.
Do you guys agree with me or do you prefer to do it another way?
If so, can you help me to do a parameter and apply it to that measure?
Thanks a lot guys for all your incredible help.
@pedroccamara what is logic behind all these values? If I understand you don’t want to hard code these values, correct? you can create a virtual table using ADDCOLUMNS and then use IN condition.
Here is an example
Sales GT 3 =
VAR BrandAndSales =
ADDCOLUMNS ( ALL ( Products[Brand] ), "@SalesAmount", [Total Sales] )
VAR FilterProducts =
FILTER ( BrandAndSales, [@SalesAmount] > 300000000 )
VAR GetBrands =
SELECTCOLUMNS ( FilterProducts, "@Brands", Products[Brand] )
VAR Result =
CALCULATE ( [Total Sales], Products[Brand] IN GetBrands )
RETURN
Result
HI @pedroccamara,
If you do this in power query than you are only loading data across these parameters. Obviously you will be using these values to filter a column in a table and than loading it.
However, i would advice to do it in DAX the you have done it but instead of manually putting these values i would try to get them as column from some table using distinct function.
Something like this
Total measure = Calculate ( [totalCD], General Ledger [Account] IN Distinct(Table[Column])).
Regards,
Hemant
Hey @AntrikshSharma
Thank you for your answer. I just want to have a simpler measure than this one:
Total measure = Calculate ( [totalCD], General Ledger [Account] ) IN {“41”, “51”, “25”, “26”, “27”, “28”, “29”, “31”, “32”, “33”, “34”, “35”, “36”, “37”, “38”, “39”, “61”, 65", “66”, “67” } )
I thought it should be done with a parameter, but if it’s not how is it?
Thanks
Hello @Hemantsingh
I agree with you to do it in dax. I believe your idea (distinct ( Table[column])) is good.
Can you help me on the best way to do this table?
I will certainly help. Can you provide a sample pbix . I will try to write a formula for you.
Regards,
Hemant
I’m so sorry but this one is very large and several connections. I can’t share it easily.
But you could do it with the measure above.
My fact table is General Ledger. My filter column is Account and my measure is totalCD. If you need more, just add some common names and i’ll try to understand.
Thanks @Hemantsingh
Hi @pedroccamara,
Now when you have decided to handle this filtering in DAX, You can do it in more than one ways lemme put some ways as example for you.
First way - You can create a label column (I would advice to make it in power query) against account codes in the accounts table and than change your measure like
Total measure = Calculate ( [totalCD], KeepFilters(General Ledger [LabelColumn Name] )=“Label Name”)
Second way = Create a disconnected table only with the codes you want. lets say you call the new table with codes as SelectedCode and the column in the table for codes as selectedcodes
Total measure = Calculate ( [totalCD],General Ledger [Account] in Distinct(‘SelectedCode’[SelectedCodes]))
Secod way is more flexible as you have control to add more codes in the table in near future.
Lemme know if these ways helps you in writing a better logic than what you have already written.
Regards
Hi @Hemantsingh
I’ve just used your second option to shorten my DAX - and now it’s more readable, plus I have a table for my selection, which I can amend if required in future. Thanks 