If Condition

[Anonymous]

Hello All,

Iwas using IF condition to calculate based on condition.

While applying formula i could not able to select the comlumn name to filter the data. fpr example I've a table with column "type" , wish to filter with bundle under type

using below formula whihc is not allowing

=if(table[type]= "bundle"....... My query is, could not able to select the type column to define an expression.

Please help

REgards,

I

[v-xicai]

Hi,

You can create measure Count Clients to distinct count the [Clients], and create measure Filter1 to filter the table visual to display the [Type])="Bundle"  by putting the Filter1 in the Visual Level Filter of table visual ,setting Filter1 as "is not blank".

Count Clients = CALCULATE(DISTINCTCOUNT(Table1[Clients]),FILTER(Table1,Table1[Type]=MAX(Table1[Type])&&Table1[Clients]=MAX(Table1[Clients])&&MONTH(Table1[Date])=MONTH(MAX(Table1[Date]))))

Filter1= IF(MAX(Table1[Type])="Bundle",1,BLANK())

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

it doesnt worked. Please help with other logic

REgards,

[v-xicai]

Hi @Anonymous ,

Could you please show me the error information form my pbix file? 

For the error as you said above :

MAX function dint work, shows error as many aurguments passed for MAx function.

To work around the issue ,you can check if missing brackets ")".

Or could you please share your sample data or screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

[parry2k]

@Anonymous are you adding a measure or column? if you are adding a column, it will work.

[Anonymous]

Hi All,

I'm adding a measure, please help me with a solution for this.

Need to add a measure becoz calculated column will not work for my data since i've other complications, hence should use Measure.

1. need to count unique clients for each month, i used DIstinctCount(client).

2. For Type = Bundle, it should take the count from the Count Column, for example, for Client A the clint count for May month is 50 and April 65.

3. For all Regular Client count should Client Column by using DistinctFunction.

i've created Distint count ( Disctinct Cout of column Client) as a single measure and Sum of Bundle clients (Sum(count)) as anotheer Measure, and trying to create a new measure by applying IF condition, as below 

If(Table[Type]="Bundle",(distinctcountmeasure), (sumof Bundle measure))

Please correct me and advise the solution accrordingly.

REgards

[Ashish_Mathur]

Hi,

Does this measure work?

=IF(HASONEVALUE(Table[Type]),If(VALUES(Table[Type])="Bundle",(distinctcountmeasure), (sumof Bundle measure)),BLANK())

[Anonymous]

Hello Ashish,

I dint get any value.

REgards

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

Hello Ashish,

I could able to share sample data which is similar to my data. 

I've data from fy14 till date and month is recorded as first day of the month pattern.

've client with size, if you abservve the client 3 whihc is a bundled type implies has say 500 clients (from columnH), my req is if i need the count for each month Client1,client2, client4 should display as 1 but for client3 it should count as 500 for month of April and 505 in May any dynamically change monthly.

Please let me know if you need further details.

https://drive.google.com/file/d/1BAfwWWw-_caH8VQXl_l3h1nckvfQ_WjI/view?usp=sharing 

REgards

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Hello Ashish,

Thanks for your response, but it has little bug.

When i was trying to calculate the count for each month based on category (below screenshot is from the PBXI file"

1. 've a fields under type as "Big-Size" the client counts are not captured from the point where the sum condition is applied since all the bundled clients are BigSize Clients.

2. the Row level sub total is not captured as showh above.

3. I wish to check the type level of reporting like Small, Big Size etc.. as per observation, when both the conditions are applied  

Regards,

Varun

[Ashish_Mathur]

Hi,

Show me the exact result you are expecting.

[Anonymous]

Hello Ashish,

as mentioned above format, need to present the count at category level.

[Ashish_Mathur]

Hi,

I just do not understand.  May be someone else will help you.

[Anonymous]

Hello Ashish,

My requirement is to find the client count for each month under each category,hence above is the table format which shows the client count for each month and undr each category.

you solution is really close but if you observe in the DAX and the table sample  report wich i posted in my previous table, it doesnt show the final count since when the If formula is calculating the counr of Bundles.

Please let me know if you need further details.

REgards,

[Ashish_Mathur]

Hi,

As i have requested earlier, can you please tell me the exact numeric result you are expecting so that once i re-write my DAX formula, i can cross check my result with yours.

[Anonymous]

Hello Ashish,

Thanx for your time It helped me to get my solution.

[parry2k]

@Anonymous if you want to add as measure, you need use aggregation for your type in calculation, something like this

If(MAX(Table[Type])="Bundle",(distinctcountmeasure), (sumof Bundle measure))

[Anonymous]

Hi ,

MAX function dint work, shows error as many aurguments passed for MAx function.

REgards.