Need a quick Dax to create a measure based on rows conditionally selected to a table

[Anonymous]

I have this table where clients are selected if total confidence score for the client is greater than 50.  

I  have created a working measure "*Total confience score Taskcode grouped" and pulled to filter pane.

Now, I need to aggregate the column "Codes used by firm" into card "Total number of codes billed for all your clients".  This card only sums clients selected (in table) based on condition that confidence score for each of the clients must be greater than 50.  

Here is the PBIX:

https://1drv.ms/u/s!AlMdRxAveLesgcFiJmwq6E7lmsPLWA?e=sMg3rf

[Anonymous]

Hi @Anonymous 

Has your problem been solved ?

If no, please provide your current problem .

If it has been solved, provide your method for the problem then consider Accept it as the solution to help the other members find it more quickly.

Best Regards

Community Support Team _ Ailsa Tao

[Anonymous]

@aj1973 

I have a DIMdate table which is disconnected (called DimDate - disconnected).  There is a second working DimDate which relates to other tables.   This is deliberate and working fine.  

Click on button to see Timeline slicer.  It is not an issue though.

Measures *Visual end month and *Visual start month point to the dimdate-disconnected table.  This is required because I need to filter DimDate using this intermediate selection from the disconneted table. If I select a month from disconected table, I want to render report on dimdate with logic of two months prior to the start date (*Visual end month) and 1 month after the end date (*Visual start month ).  This is working fine.   

[aj1973]

And

you get

You need to understand what you want to achieve with your visual to make a good solid model.

[aj1973]

For instance, if i just connect (and nothing else)

I get

Though not sure what do you expect

[Anonymous]

@aj1973  

OK, here is the reqirement again.  Using your screenshot,

1.  "Total number of codes billed for all your clients" should be equal to distinct count of column "Codes used by firm" 

2. So, to qualify for card "Total number of codes billed for all your clients", the clients total confidence score should be greater than 50.

3.  Also,  for Client to qualify to be listed in table under column "Codes used by firm " total confidence score"  for client should be greater than 50.  

@Icey   

To select rows with "total confidence score" greater than 50, can we create a Dax similar to measure:   *Except Codes Measure taskcode New  ?

[aj1973]

1- Replace DISTINCTCOUNT by COUNT

2- Same here

3- Needs attention ; Same Client, Different ID

4- Question 2 & 3

[Anonymous]

@aj1973 

As per requirement, what I need is DISTINCTCOUNT.  I do not expect the sum on summary card to be equal to total on column "Total number of codes billed for all your clients".  It should only take into account unique codes. 

It would have been great to be able to pull the new measure into visual filter of summary card and set to "Yes".    I am not able to set it. 

Also, I only need not null values for column  ("Total number of codes billed for all your clients")

[aj1973]

@Anonymous 

Sorry I don't get your point nor agree with it. Usually we add a Date table to use Time intelligence functions and filter some data from fact tables that contain Date/time columns. In your model you are filtering dates that are not related to dates from Fact tables therefore Measures, filters, pages and visuals will not interact with each others. 

[Anonymous]

@aj1973 

Let me explain the logic.  Like I said it is perfectly fine.

Please note labels.  There is a date selector button Timeline slicer.  Whatever date I select based on this is reported at the top (and connected to Dimdate - disconnected). But I need to transform the selected date to pull dataset  (Report date pull date rane - dimdate) outside of the range selected,  I need to do the transformation using connected Dimdate.  The rule is that date used by Dimdate needs to be 2 months prior to the start date and 1 month post end date selected.  

Take time to understand the requirement. It is not complex. 

[aj1973]

Again, what you do with disconnected table is your way of designing things, thats fine....what I am trying to explain to you is that you can't filter expressions into your measures with tables that are not connected.

Please follow my previous posts and do some tests, you will certainly understand. 

[Anonymous]

@viviank   That is what I did.   When I pull into the card, I cannot set the filter consdition. 

[viviank]

I can see the problem. The card visual is using the report_ProducerConsumerTaskCodes table -- but you are trying to filter using a measure that references a different table, called report ConsumerTaskCodes.

These tables are related to each other through another table - called Consumer - with 1 to Many relationships.

The filters don't flow upstream by default. The filter isn't getting to the Consumer table.

To fix this: Go to the Model, choose the connection between report ProducerConsumerTaskCodes and Consumer. Right-click and choose Properties. Set Cross filter direction to Both (not Single).

After you do this, you can add the measure as a filter to the card visual.

[viviank]

I would try turning "Total Confidence Score Taskcode Group By" into a Measure - and then use the Measure to filter both visuals - the table and the card.

[Anonymous]

@viviank   Thanks.   The PBIX is attached.  Can you please try it out to see if it works?  Your suggestion  is not working for me.   

[viviank]

Sorry, I thought I had it working but I'm stuck too.I see an issue thought that needs to be fixed. Here is your measure:

*Total confidence score Taskcode grouped =

CALCULATE(SUM('report ConsumerTaskCodes'[confidenceScore]), FILTER(DimDate,DimDate[Calendar]>=[*Visual start month] &&DimDate[Calendar] <= [*Visual end month]))

There is no connection between DimDate and the other tables. It doesn't make sense to filter 'report ConsumerTaskCodes' by another table 'DimDate' when there is no connection.

You'll need to set up the connections with the DimDate table.

Unfortunately this is not enough to get the measure filter on the card visual. There is still an issue with that. I don't know how to fix it but I think it's because you are filtering for Consumers and the card table is for Producers.

Good luck! I'm stuck 😞

[Anonymous]

@viviank 

DImDate is connected.   The only date table that is deliberately unconnected is "DimDate - disconnected".    Thanks fir the efforts. Appreciated. 

[aj1973]

Hi @Anonymous 

What is this measure for?

You are using filters on DimDate while the table is not related to any of your tables!!

[Anonymous]

@aj1973 

Wow!  I did not notice that DimDate table got inadvertenty disconnected while I was trying to create a sample PBIX for community help purpose.    Sorry about that. 

Please join Calendar column on DimDate to submissionMonth on both report consumerTaskcodes and ProducerconsumerTaskcodes tables.  You can then see how to address the issue.     

I will try to update the link on Onedrive later.  Thanks. 

PBIX  Here:  https://1drv.ms/u/s!AlMdRxAveLesgcFiJmwq6E7lmsPLWA?e=sMg3rf