How to create a measure on a dimension column?

[Anonymous]

Hi,

I need to show a drop down between a date range on a dimension called 'Service Cateory' and I have created a measure for that:

Service Cat List = CALCULATETABLE(DISTINCT(Data['SERVICE_CATEGORY']), Data[Creation Date]>=Data[From Date],

Data[Creation Date]<=Data[To Date] )

so basically it will return the values of service category between that date range.

but the above DAX gives me an error like below:

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

So may I know how can i achieve this?

[TeigeGao]

Hi @Anonymous ,

Actually, it is not hard to use a measure to filter a table, you can create a measure using the following DAX query:

Service Cat List = IF( MIN(Data[Creation Date])>=Data[From Date] && MIN(Data[Creation Date])<=Data[To Date] ),1,0)

Then we can use this measure as a filter and set the value >= 1, then all measure <1 will be filtered out.

Best Regards,

Teige

[Anonymous]

Hi,

I have created a measure :

Filter Page = IF( MIN(Data[Creation Date])>=Data[From Date] && MIN(Data[Creation Date])<=Data[To Date] ,1,0)

and used it as visual fiter like below:

The table on which I am using the above filter is showing all 1 for that measure field:

So it is notr working well. Please guide me this case.

Also I have given the link below to view my working pbix file and you can see my work at Page 2

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

[Anonymous]

@Anonymous -

The following code is not a Measure, but a Calculated Table:

Service Cat List =
CALCULATETABLE (
    DISTINCT ( Data['SERVICE_CATEGORY'] ),
    Data[Creation Date] >= Data[From Date],
    Data[Creation Date] <= Data[To Date]
)

When do you get the error, exactly?

Cheers!

Nathan

[Anonymous]

Hi,
If you think that my DAX is not correct for my requirement, Can you please change it and give it to me. It is very urgent for me to show values like this.

[Anonymous]

@Anonymous - Sorry, I won't be able to get to this today. I'll take a look tomorrow, unless you get it resolved. Creating a new thread may increase the chances of somebody else responding.

[Anonymous]

Hi,

I have modified the DAX to the below:

Service Cat List = CALCULATE(DISTINCT(Data[SERVICE_CATEGORY]),

Filter('Data', Data[Creation Date]>=Data[From Date] &&

Data[Creation Date]<=Data[To Date] ))

and created a measure. Now if I want to create a filter drop down using it, I cannot do that.

Also my requirement is that I want to create a drop down using 'Service Category' column and

the values will be be between From Date and To Date.

Please suggest what to do that?

[Anonymous]

@Anonymous - I'm not sure exactly what you want to do, but it sounds like you want to:

1. Select dates and have that filter categories

2. Select categories to filter something else.

If this is the case, then you will need a category table, including relevant dates. Note: this table is not filtered when you create it.

Then you can filter this table so that only the desired categories show up.

The categories are columns of the table, not measures. That means they can be used as filters, as well.

Cheers!

Nathan

[Anonymous]

Hi,

I have copied the App link that I have created and you have some time for me, please go thorough the App.

https://drive.google.com/open?id=1B-8KctkkTGVHjF3bTB9MaFIQ2OyBKK19

Now at page 2, we have one input tet box and when user put date into it and select a date Range from drop down, it will automatically create From Date and To Date. Now I want that the Service Category drop down value will be changed based on that date range.

According to your suggestion, i need to create one pivot table with Category Name as column name and Date as values and use it for the drop down. So it will be easy to pick up for me if you create that thing in my uploaded App.

Please help me in this case.