Python to DAX Conversion
Hello, We are trying to convert the following python code into DAX. The goal is to distinct count of case number (caseno) grouped by (year), where occ date/time (occuron) is like YYYY Any help or assitance is greatly appreciated. Anything bolded is the column name in our dataset. Anything underlined denotes the table. The tables and columns would be indicated in DAX like: (e.g., 'table' and [column] would come from this: 'wa offense'[caseno] SELECT date_format(occuron, '%Y') AS occuronyear, count(DISTINCT caseno) AS dis_caseno FROM wa offense GROUP BY date_format(occuron, '%Y') ____________________ Specfic ibroff category: SELECT date_format(occuron, '%Y') AS occuronyear, count(DISTINCT caseno) AS dis_caseno, ibroff FROM wa offense GROUP BY date_format(occuron, '%Y'), ibroff Alternatively, is there a way to utilize python instead of DAX? Thank you!Converting seconds to HH:MM:SS text format repeatedly
I have a DAX measure to convert my [Duration in sec] to hh:mm:ss text format. However, if I have several measures which I need to convert to hh:mm:ss text format, e.g. [call Duration in sec], [call Duration in sec wk], [email duration in sec], [email Duration in sec wk], is there a way that I will only create one DAX measure 'Time in Text' that formats them all? Time in Text = VAR Seconds = [Duration in sec] VAR Minutes = INT ( Seconds / 60 ) VAR RemSeconds = MOD ( Seconds, 60 ) VAR Hours = INT ( Minutes / 60 ) VAR RemMinutes = MOD ( Minutes, 60 ) RETURN Hours & ":" & RemMinutes & ":" & RemSeconds
Find the Forex Rate for the last day of the month for any transactions within that month
I am trying to create a measure that will find and use the Forex Rate from the last day of the month for any transaction within that month. I have the below measure which works for finding the Forex Rate that matches the transaction date for transactions that are related to Revenue and Cost of Sales (and accounts for gaps in Forex Rates over weekends). However, for internal General Ledger Journals, we need to use a general reporting Forex Rate, which we input into our system, with an end of month date (29, 30, or 31, etc.). How do I amend the below measure to find the Forex Report Rate at the end of each month for those General Ledger Journals with transaction dates within that month? Fx to USD Report = IF( SELECTEDVALUE( GLTrans[BaseCurr] ) = "USD", 1, MAXX( TOPN( 1, FILTER( ALL( ForexRates ), ForexRates[Base Currency] = SELECTEDVALUE( GLTrans[BaseCurr] ) && ForexRates[Forex Date] <= MAX( GLTrans[DateTrans] ) ), ForexRates[Forex Date], DESC ), ForexRates[Report Rate] ) )