Forum Discussion

campelliann's avatar
campelliann
Icon for Post Patron rankPost Patron
4 years ago
Solved

New column returning always the same value on a virtual table

Hi there,


I have this table with project data, where each project lives on one row. I have several columns for each row, amongs them the beginning and ending date.

 

Table Sample

Project IDBeginning Date (Projeccoes Data inicio in dax)End date (produção in dax code below)
Project 11 jun30 jun
Project 21 may 31 may


Expected Output

Project IDBeginning Date (Projeccoes Data inicio in dax)End date (produção in dax code below)@Total days
Project 11 jun30 jun30
Project 21 may 31 may31

 

Error I am getting: 

Project IDBeginning Date (Projeccoes Data inicio in dax)End date (produção in dax code below)@Total days
Project 11 jun30 jun944
Project 21 may 31 may944


So I basically need to create a virtual table where I add a new column with the total days of duration for each project. So I created this Dax code below, but when I go to check how the virtual table looks (creating a phyical table based on the code) I get the same duration for every project. It seems the code is not having a "row context" :

var current_project = SELECTEDVALUE('Project Table'[Project ID]) // current ID in the present row

 

var dates_project = calculate(countrows(DATESBETWEEN('Project Table'[Data Criação Project ID],SELECTCOLUMNS(filter('Project Table','Project Table'[Project ID]=current_project),"N",SELECTEDVALUE('Project Table'[Projecções Data Início])),SELECTCOLUMNS(filter('Project Table','Project Table'[Project ID]=current_project),"N2",SELECTEDVALUE('Project Table'[Produção])))))           // filters the initial table based on the current row project ID and then selects the Beginning date, and ending date. Finally I use countrows to count the number of days

var tabela_virtual = ADDCOLUMNS('Project Table',"@Total days",dates_project)))

 

Return tabela_virtual

 

 

  • campelliann I believe you want something like this:

    Table =
      VAR __Project = SELECTEDVALUE('Triskall Complete'[Process])
      VAR __Table = 
        ADDCOLUMNS(
          FILTER('Triskall Complete',[Process] = __Project),
          "__Days",([End Date] - [Start Date]) + 0
        )
    RETURN
      __Table

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion

    campelliann Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion

    Hi,

    Try this kind of DAX:

    ADDCOLUMNS(Projects_v,"Time",DATEDIFF(Projects_v[Start],Projects_v[End],DAY)+1)

    Example:



    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

    • campelliann's avatar
      campelliann
      Icon for Post Patron rankPost Patron

       

      Hi ValtteriN. First and foremost thanks, I have been around this problem for a couple of days and this driving me crazy 🙂 Now I get only 1s in the last column. Note that I am trying to build a Virtual Table for a more complex problem. I just created this physical table to check how the virtual table looks. It seems that in the virtual table I am not creating a row by row count of the days... 

      • ValtteriN's avatar
        ValtteriN
        Icon for Community Champion rankCommunity Champion

        Hi campelliann 

        I think the problem is with the SELECTEDVALUE. In the filter context you are applying it now it returns blank. 

        You don't often need the intermediary var step in your virtual table.

        E.G.

        Datediff from virtual table = var _table = ADDCOLUMNS(Projects_v,"_Time",DATEDIFF(Projects_v[Start],Projects_v[End],DAY)+1) return

        SUMX(_table,[_Time])