cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Condition Set , Count both to 1 , and one of code to 1

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 

1.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
d_gosbell Senior Member
Senior Member

Re: Condition Set , Count both to 1 , and one of code to 1

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] )

6 REPLIES 6
d_gosbell Senior Member
Senior Member

Re: Condition Set , Count both to 1 , and one of code to 1

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.

 

Re: Condition Set , Count both to 1 , and one of code to 1

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 ? 

 

1.PNG

d_gosbell Senior Member
Senior Member

Re: Condition Set , Count both to 1 , and one of code to 1

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? 

Re: Condition Set , Count both to 1 , and one of code to 1

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. 

d_gosbell Senior Member
Senior Member

Re: Condition Set , Count both to 1 , and one of code to 1

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] )

Re: Condition Set , Count both to 1 , and one of code to 1

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