topic Re: Need to create a measure - find the maximum identifier MAX ([ID] OVER [CASE_ID]) in DAX Commands and Tips

Need to create a measure - find the maximum identifier MAX ([ID] OVER [CASE_ID])

Anonymous — Tue, 16 Jul 2019 06:01:12 GMT

HI, I have a table F_JOB_STATUS:

my task:
A/B*100
A = cases ([case_id]) (only [case_type_id] = 1 || 2) solved ([case_status_id] = 7 && [DE_ID] <> BLANK()) in shorter period ([WTF] = 1) during that month
B = All solved cases ([case_status_id] = 7 &&, [DE_ID] <> BLANK())

As you can see, the id in the table is duplicated, you need to take into account the unique ones (for this, I think, need to find the id, like so: MAX([ID] over [CASE_ID]))

To find the maximum id, I created a column

OPEN_C&C_WTF_MAX (F_JOB_Status) = 
CALCULATE(MAX(F_JOB_STATUS[ID]),FILTER(F_JOB_STATUS,F_JOB_STATUS[CASE_ID]=EARLIER(F_JOB_STATUS[CASE_ID])))

but it did not help me, since it is a column and when filtering by date it loses its meaning

Is it possible to create 2 measures A and B, in which conditions A and B are taken into account and the maximum id is taken into account?
or maybe there is another way?

MY pbix file

Help me

Re: Need to create a measure - find the maximum identifier MAX ([ID] OVER [CASE_ID])

Anonymous — Mon, 15 Jul 2019 19:46:12 GMT

Mate,

You want measures... That's OK but what are you going to slice the data by? Do you understand what the difference is between a measure and a calculated column?

Second, you say you want to filter by date... Do you have a Date table in your model connected to your fact table? If a single date is visible, then I could probably write the measures you want. But what if you select a whole month, like 2019-Jun? What should the measures return? Please, remember that when you create measures they should work correctly for any slicing of your dimensions. Please, kindly tell me if you really want a measure or a calculated column... It's not really clear what you want at this point.

I'll have a look at the file as well but it would be good if you could clarify things.

Best

Darek

Re: Need to create a measure - find the maximum identifier MAX ([ID] OVER [CASE_ID])

Anonymous — Mon, 15 Jul 2019 19:56:57 GMT

It looks like the values for the A and B measures in your table DO NOT correspond to the description of how to get them... unless I don't understand. But the very fact that I don't understand how you arrive at the numbers after reading the description several times means that you should post something that is human-readable and as clear as the day instead of forcing others to spend hours to understand "what you meant." If you do that, please do not expect any answers.

Please try to be precise when you describe a problem. Mathematics should have taught you that.

Best

Darek

Re: Need to create a measure - find the maximum identifier MAX ([ID] OVER [CASE_ID])

Anonymous — Tue, 16 Jul 2019 05:39:33 GMT

Good day, Anonymous
I still could not solve the problem. I need exactly the measure, because the measure will determine the maximum identifier after filtering by date.

And after that, I'm going to Create another measure:

measure C = Distinctcount[Measure A] / Distinctcount[Measure B]

With this, I will solve the problem.

And, yes, I understand the difference between the measure and the column.

I am new to BI reporting. At first I created a column and, after examining the result, I realized that I needed a measure.

Re: Need to create a measure - find the maximum identifier MAX ([ID] OVER [CASE_ID])

Anonymous — Tue, 16 Jul 2019 09:04:09 GMT

Hi there.

There is no such thing like:

measure C = Distinctcount[Measure A] / Distinctcount[Measure B]

DISTINCTCOUNT is a function that works on columns, not measures -> DISTINCTCOUNT documentation

I'll give you a hint if you want your question answered. Here it is.

Best

Darek

Re: Need to create a measure - find the maximum identifier MAX ([ID] OVER [CASE_ID])

Anonymous — Tue, 16 Jul 2019 14:14:26 GMT

Anonymous

I apologize, do not judge strictly, I am new to this forum and to POWER BI. And yet, I have a bad English, so I use a online translator.
Rewrote my post below, please see.

I have a call accounting table (Demo data1). There are indicated:
1. ID
2. Treatment ID - [CASE_ID]
3. Type of treatment - [CASE_TYPE_ID]
4. Appeal status - [CASE_STATUS_ID]
5. Within Time Frame - [WTF]
6. id result - [DE_ID]
7. date of appeal status - [DATE]
8. institution ID - [I_ID]

my task:
A/B*100
A = cases ([case_id]) (only [case_type_id] = 1 || 2) solved ([case_status_id] = 7 && [DE_ID] <> BLANK()) in shorter period ([WTF] = 1) during that month
B = All solved cases ([case_status_id] = 7 &&, [DE_ID] <> BLANK())

I have to create a histogram, where [I_ID] axis, and values A * B / 100
filters will be by date (still by main authorities and for whose personnel I have not added to the test table)

I’m having trouble “thinking in DAX”.

I ask for help.

Re: Need to create a measure - find the maximum identifier MAX ([ID] OVER [CASE_ID])

Anonymous — Wed, 17 Jul 2019 04:47:11 GMT

OK, first of all, you should create all the dimensions that will be joined to the fact table. When this is properly done, then you can write this:

-- The fact table should have dimensions connected to it
-- and slicing should be by the dims, not by the columns
-- in the fact table. The measures below overwrite
-- filters that would be put on the fields used in the
-- boolean conditions. If you want to keep existing filters
-- you have to add KEEPFILTERS to some/all the lines.

[A] =
calculate(
	-- I don't think you really wanted the maximum case_id.
	-- To me it does not make any sense. Counting the
	-- distinct case_id's, on the other hand, does make
	-- sense.
	distinctcount( FT[case_id] ), 
	'Treatment Type'[case_type_id] in {1,2},
	'Appeal Status'[case_status_id] = 7,
	'Result'[de_id] <> BLANK(),
	'Within Time Frame'[WTF] = 1
)

[B] =
calculate(	
	distinctcount( FT[case_id] ),
	'Appeal Status'[case_status_id] = 7,
	'Result'[de_id] <> BLANK()
)

-- You should not multiply this by 100. You should
-- change the formatting of this measure in Power BI.
-- This is the right way to do it.

[Final Measure] =
divide( [A], [B] ) 

-- If you want to KEEP existing filters on some of
-- the columns, you should write something similar
-- to this:

[A] =
calculate(
	distinctcount( FT[case_id] ),
	keepfilters( 'Treatment Type'[case_type_id] in {1,2} ),
	keepfilters( 'Appeal Status'[case_status_id] = 7),
	'Result'[de_id] <> BLANK(),
	'Within Time Frame'[WTF] = 1
)

-- If any filters exist on 'Treatment Type'[case_type_id]
-- then keepfilters( 'Treatment Type'[case_type_id] in {1,2} )
-- makes sure that the filters are not removed but intersected
-- with the condition 'Treatment Type'[case_type_id] in {1,2}.

-- Since you've not given me enough information on how this
-- should really work, this is the best I can do. If this
-- does not work the way you wanted, you have to always ensure
-- that you give as much info as possible.

Best

Darek

Re: Need to create a measure - find the maximum identifier MAX ([ID] OVER [CASE_ID])

Anonymous — Wed, 17 Jul 2019 06:22:58 GMT

Anonymous

Thank you very much. At first glance, everything works correctly. I will carefully check and if I have questions, I will write.
Thanks again.