Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All ,
I have a lots of different Code Numbers in the data and would like to have some condition with multiple code numbers , I wanna set condition , if both of 1317 and 1108 , have in the data we count only 1 not 2 , but if one of them have in the data we count 1 . this is just for 2 codes only i gave example , if i have multiple code number to be the same , what measure should i do ?
therefore in master data i have
Solved! Go to Solution.
Given the output you want I believe that my first answer is still the correct one. What you have here is not a formula problem, it's a data modelling problem. If you create a table like the following, Then create a relationship between the Code in this table and the code in your existing table.
Code Group
1108 Coke
1263 Coke
3497 Fanta Fruit Punch
6367 Fanta Fruit Punch
3089 Fanta Orange
2483 Fanta Orange
2090 Sprite
4883 Sprite
I'm guessing based on your output that the 1 and 0 data is actually meant to be a distinct count of stores that stock given products. If my guess is correct you could create a formula like the following (replacing the <table> placeholder with the real name of your table). And then use this measure in a matrix visual with your store columns on the rows and the code and group on the coloumns.
Store Count = DISTINCTCOUNT( <table>[Customer Name - Store host code] )
If this is just an example and you have many codes that need to be combined I don't think you should be doing this in a measure. This sounds like a modelling issue and you should probably create a mapping or lookup table that does this grouping,
eg.
Code ParentCode
=== =======
1317 Parent A
1108 Parent A
4223 4223
...
Then if you create a relationship between the code column in this table and your master table, when you drag in the ParentCode column it will group by that and do any measure calculations over those groups.
HI @d_gosbell sorry , your response was jumped into junk email which i just realized it.
Anw what my desire result on excel is using = If(OR(AB5=1,AC5=1),1,0)
And i wish BI Can work it out same as excel , And i have many different code which need to be applied the same.
can you please guide me to acheive this ?
Sorry, but something has been going wrong with the notifications for my posts this week and they've only just started working again.
There is not enough information to be able to help you here. What table are these codes in? Are they in the same table as the customers or stores that are on the rows of your example output?
Hi @d_gosbell This is excel file which is calculated with IF(OR) etc... in green column as the desire result. U see I have made a lots of calculation with green columns with different code numbers.
and I have the same table on BI but doesn’t have green column as the calculation as I shown on excel.
My expectation is to calculate on BI as the same result as Excel I made on IF (OR) calculation.
On green column on excel Calculated with the code numbers on white columns, I have different code number which need to do the same things in excel , beside these code number we will sum the QTY as normal . But in exception, use above excel calculation.
Given the output you want I believe that my first answer is still the correct one. What you have here is not a formula problem, it's a data modelling problem. If you create a table like the following, Then create a relationship between the Code in this table and the code in your existing table.
Code Group
1108 Coke
1263 Coke
3497 Fanta Fruit Punch
6367 Fanta Fruit Punch
3089 Fanta Orange
2483 Fanta Orange
2090 Sprite
4883 Sprite
I'm guessing based on your output that the 1 and 0 data is actually meant to be a distinct count of stores that stock given products. If my guess is correct you could create a formula like the following (replacing the <table> placeholder with the real name of your table). And then use this measure in a matrix visual with your store columns on the rows and the code and group on the coloumns.
Store Count = DISTINCTCOUNT( <table>[Customer Name - Store host code] )
Hi @d_gosbell thanks for your help , you wake me up , i was distracted for a while without knowing this function already have in place.
anw i did as per your mentioned and it works well. thx you
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |